Mapping the Carbon Intensity Data

In last weeks’ post (UK Energy Supply Monitoring) we showed how we can pull data relating to the carbon intensity of the the electrical power output of Great Britain. In this week’s tip we show how we can visualise that data on a map of Great Britain so you can see which regions have carbon intensive energy production. Of course, this is Splunk so it’s not a point in time view, the result is a live view that will update over the course of the day.

Download the Geodata File

There are fourteen Distribution Network Operators (DNO) regions in Great Britain. In order to be able to position the data values correctly on a map of GB we need to know the boundaries of these regions. We can download a file that defines these GIS geospatial boundaries. These regions are the same set of regions that are reported against in the carbon intensity data. Download the geojson version of the file from the link below:

https://data.nationalgrideso.com/system/gis-boundaries-for-gb-dno-license-areas

Note that in order to use our data on a Splunk Choropleth map we need the geospatial data to be in the KMZ format. So we need to go through a short conversion process.

Converting the Geodata File

There are various tools available to help you convert geodata files. We found that this site worked well and allowed us to convert the geojson file to KMZ within the 5MB free limit (get it right first time). We set the NAME field to be LongName as can be seen in the screenshot below.

https://mygeodata.cloud/converter/

Creating the Geospatial Lookup

We then add the KMZ file as a lookup file, like we would normally do with a csv lookup:

We then create the lookup definition. This is where we tell Splunk that this isn’t a normal csv lookup but is actually a geospatial file:

You can check that the lookup is working OK by using the inputlookup command:

Building the Search

The basic version of the search that builds the stats table required looks like this:

index=main sourcetype="carbon_intensity:regional" NOT dnoregion IN ("Scotland","England","Wales","GB")
|stats  latest(intensity.forecast) as count by dnoregion

This produces a stats table like the one below. In order for the lookup to work we need to match the dnoregion field to the featureId in the lookup (so that we can attribute the carbon intensity value to a multi-polygon region on the map). As you can see the names are not an exact match. It is important to note that the data column name is count, this is a requirement of the choropleth visualisation. Previously we had the column named “intensity” but this didn’t work, some things you have to learn the hard way!

Fixing the Data

Unfortunately the region names in the carbon intensity data are not exactly the same as those provided in the geojson file. We could edit the file and take a step or two back but let’s fix that using an eval, mapping the region names in the data to the region names in our kmz file. We will call the new field featureId as that is the field name that the geom command uses by default. The eval used can be seen in the next section.

Create the Visualisation

The search now has the eval added and finally we use the geom command to perform the geospatial lookup. This adds the geometric definitions of each region into the stats table, which is used by the choropleth visualisation to draw the areas.

index=main sourcetype="carbon_intensity:regional" NOT dnoregion IN ("Scotland","England","Wales","GB")
|eval featureId=case(dnoregion="Electricity North West","ENWL",dnoregion="NPG North East","NPG (Northern Electric)",dnoregion="NPG Yorkshire","NPG (Yorkshire Electric)",dnoregion="SP Distribution","SPEN (SP Distribution)",dnoregion="SP Manweb","SPEN (SP MANWEB)",dnoregion="Scottish Hydro Electric Power Distribution","SSE",dnoregion="SSE South","SSE (Southern)",dnoregion="UKPN East","UKPN (East)",dnoregion="UKPN London","UKPN (London)",dnoregion="UKPN South East","UKPN (South East)",dnoregion="WPD East Midlands","WPD (East Midlands)",dnoregion="WPD South Wales","WPD (South Wales)",dnoregion="WPD South West","WPD (South West)",dnoregion="WPD West Midlands","WPD (Midlands)",true(),null())
|stats  latest(intensity.forecast) as count by featureId
|geom dno_license_areas allFeatures=true

Finally we can select the Choropleth Map Visualisation option. The initial result could look something like this:

Not ideal but can be fixed with a few options:

• Scroll and zoom the map so that the British Isles are centralised
• Click on format and hit “Populate with current map settings”
• Click on colors and ensure Color Mode = Sequential, choose a bright red for the maximum color
• Change the number of bins to your preference (8 or 9 works best)
• Click on Tiles then Populate from preset configuration, choose Open Street Map
• Click on Shapes and set Shape Opacity to 50% (this lets more of the map show through), you can tune this to your preference
• Save as a dashboard then drag down the panel sizing control to make the map bigger and adjust pan and zoom as appropriate, clicking on Populate with current map settings again to set,
• Save your final adjustments to the dashboard
• Edit the SimpleXML and add a refresh to have the map update automatically (if new data is available)
  • <dashboard refresh=”300″>

End Result

The end result looks like the image below.